﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace MSTutHelloWebAPI.Models
{
    public class DBHelper
    {
        /// <summary>
        /// Retrieve all cases from database
        /// </summary>
        /// <returns></returns>
        public static List<Case> GetAllCases()
        {
            List<Case> caselist = new List<Case>();

            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["MunicipalFeedbackConnectionString1"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM [MunicipalFeedback].[dbo].[Case]";

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Case c = new Case();
                    c.caseID = Convert.ToString(dr["CaseID"]);
                    c.agencyCaseID = Convert.ToString(dr["AgencyCaseID"]);
                    c.feedbackDate = Convert.ToDateTime(dr["FeedbackDate"]).ToString("dd-MMM-yyyy");
                    c.receivedDate = Convert.ToDateTime(dr["ReceivedDate"]).ToString("dd-MMM-yyyy HH:MM");
                    c.dueDate = dr["DueDate"].Equals(DBNull.Value) ? "" : Convert.ToDateTime(dr["DueDate"]).ToString("dd-MMM-yyyy");
                    c.closedDate = dr["ClosedDate"].Equals(DBNull.Value) ? "" : Convert.ToDateTime(dr["ClosedDate"]).ToString("dd-MMM-yyyy HH:MM");
                    c.closureDesc = Convert.ToString(dr["ClosureDesc"]);
                    c.mCustID = dr["MCustID"].Equals(DBNull.Value) ? null : Convert.ToString(dr["MCustID"]);
                    c.custName = Convert.ToString(dr["CustName"]);
                    c.email = Convert.ToString(dr["Email"]);
                    c.phone = Convert.ToString(dr["Phone"]);
                    c.NRIC = Convert.ToString(dr["NRIC"]);
                    c.caseStatus = Convert.ToString(dr["CaseStatus"]);
                    c.category = Convert.ToString(dr["Category"]);
                    c.subCategory1 = Convert.ToString(dr["SubCategory1"]);
                    c.subCategory2 = Convert.ToString(dr["SubCategory2"]);
                    c.feedbackType = Convert.ToString(dr["FeedbackType"]);
                    c.channel = Convert.ToString(dr["Channel"]);
                    c.caseAddress = Convert.ToString(dr["CaseAddress"]);
                    c.customerAddress = Convert.ToString(dr["CustomerAddress"]);
                    c.postalCode = Convert.ToString(dr["PostalCode"]);
                    //c.customerPostalCode = Convert.ToString(dr["CustomerPostalCode"]);
                    c.longitude = dr["Longitude"].Equals(DBNull.Value) ? null : (Double?)dr["Longitude"];//?
                    c.latitude = dr["Latitude"].Equals(DBNull.Value) ? null : (Double?)dr["Latitude"];//?
                    c.subject = Convert.ToString(dr["Subject"]);
                    c.description = Convert.ToString(dr["Description"]);
                    c.source = Convert.ToString(dr["Source"]);
                    c.timesRouted = Convert.ToInt16(dr["TimesRouted"]);
                    c.isViewed = Convert.ToBoolean(dr["IsViewed"]);
                    c.caseOwner = Convert.ToString(dr["CaseOwner"]);
                    c.jointID = Convert.ToString(dr["JointID"]);
                    //c.jointID = dr["CustomerAddress"].Equals(DBNull.Value) ? "" : (string)dr["JointID"];
                    c.jointDescription = Convert.ToString(dr["JointDescription"]);
                    c.lastUpdateDateTime = Convert.ToDateTime(dr["LastUpdateDateTime"]).ToString("dd-MMM-yyyy HH:MM");
                    c.lastUpdateAgency = Convert.ToString(dr["LastUpdateAgency"]);
                    c.duplicateMasterCaseID = Convert.ToString(dr["DuplicateMasterCaseID"]);
                    c.duplicateID = dr["DuplicateID"].Equals(DBNull.Value) ? null : (int?)dr["DuplicateID"];//?

                    caselist.Add(c);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return caselist;
        }

        /// <summary>
        /// Retrieve all cases from database
        /// </summary>
        /// <returns></returns>
        public static List<Case> GetCasesByCaseOwner(string caseOwner)
        {
            List<Case> caselist = new List<Case>();

            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["MunicipalFeedbackConnectionString1"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM [MunicipalFeedback].[dbo].[Case] WHERE [CaseOwner]=@CaseOwner";
                cmd.Parameters.AddWithValue("@CaseOwner", caseOwner);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Case c = new Case();
                    c.caseID = Convert.ToString(dr["CaseID"]);
                    c.agencyCaseID = Convert.ToString(dr["AgencyCaseID"]);
                    c.feedbackDate = Convert.ToDateTime(dr["FeedbackDate"]).ToString("dd-MMM-yyyy");
                    c.receivedDate = Convert.ToDateTime(dr["ReceivedDate"]).ToString("dd-MMM-yyyy HH:MM");
                    c.dueDate = dr["DueDate"].Equals(DBNull.Value) ? "" : Convert.ToDateTime(dr["DueDate"]).ToString("dd-MMM-yyyy");
                    c.closedDate = dr["ClosedDate"].Equals(DBNull.Value) ? "" : Convert.ToDateTime(dr["ClosedDate"]).ToString("dd-MMM-yyyy HH:MM");
                    c.closureDesc = Convert.ToString(dr["ClosureDesc"]);
                    c.mCustID = dr["MCustID"].Equals(DBNull.Value) ? null : Convert.ToString(dr["MCustID"]);
                    c.custName = Convert.ToString(dr["CustName"]);
                    c.email = Convert.ToString(dr["Email"]);
                    c.phone = Convert.ToString(dr["Phone"]);
                    c.NRIC = Convert.ToString(dr["NRIC"]);
                    c.caseStatus = Convert.ToString(dr["CaseStatus"]);
                    c.category = Convert.ToString(dr["Category"]);
                    c.subCategory1 = Convert.ToString(dr["SubCategory1"]);
                    c.subCategory2 = Convert.ToString(dr["SubCategory2"]);
                    c.feedbackType = Convert.ToString(dr["FeedbackType"]);
                    c.channel = Convert.ToString(dr["Channel"]);
                    c.caseAddress = Convert.ToString(dr["CaseAddress"]);
                    c.customerAddress = Convert.ToString(dr["CustomerAddress"]);
                    c.postalCode = Convert.ToString(dr["PostalCode"]);
                    //c.customerPostalCode = Convert.ToString(dr["CustomerPostalCode"]);
                    c.longitude = dr["Longitude"].Equals(DBNull.Value) ? null : (Double?)dr["Longitude"];//?
                    c.latitude = dr["Latitude"].Equals(DBNull.Value) ? null : (Double?)dr["Latitude"];//?
                    c.subject = Convert.ToString(dr["Subject"]);
                    c.description = Convert.ToString(dr["Description"]);
                    c.source = Convert.ToString(dr["Source"]);
                    c.timesRouted = Convert.ToInt16(dr["TimesRouted"]);
                    c.isViewed = Convert.ToBoolean(dr["IsViewed"]);
                    c.caseOwner = Convert.ToString(dr["CaseOwner"]);
                    c.jointID = Convert.ToString(dr["JointID"]);
                    //c.jointID = dr["CustomerAddress"].Equals(DBNull.Value) ? "" : (string)dr["JointID"];
                    c.jointDescription = Convert.ToString(dr["JointDescription"]);
                    c.lastUpdateDateTime = Convert.ToDateTime(dr["LastUpdateDateTime"]).ToString("dd-MMM-yyyy HH:MM");
                    c.lastUpdateAgency = Convert.ToString(dr["LastUpdateAgency"]);
                    c.duplicateMasterCaseID = Convert.ToString(dr["DuplicateMasterCaseID"]);
                    c.duplicateID = dr["DuplicateID"].Equals(DBNull.Value) ? null : (int?)dr["DuplicateID"];//?

                    caselist.Add(c);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return caselist;
        }


        /// <summary>
        /// Retrieve info from Case table in database
        /// </summary>
        /// <param name="caseID"></param>
        /// <returns>Case Object</returns>
        public static Case GetCases(string caseID)
        {
            Case c = new Case();
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["MunicipalFeedbackConnectionString1"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM [MunicipalFeedback].[dbo].[Case] WHERE CaseID=@caseID";
                cmd.Parameters.AddWithValue("@caseID", caseID);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    c.caseID = Convert.ToString(dr["CaseID"]);
                    c.agencyCaseID = Convert.ToString(dr["AgencyCaseID"]);
                    c.feedbackDate = Convert.ToDateTime(dr["FeedbackDate"]).ToString("dd-MMM-yyyy");
                    c.receivedDate = Convert.ToDateTime(dr["ReceivedDate"]).ToString("dd-MMM-yyyy HH:MM");
                    c.dueDate = Convert.ToDateTime(dr["DueDate"]).ToString("dd-MMM-yyyy");
                    c.closedDate = dr["ClosedDate"].Equals(DBNull.Value) ? "" : Convert.ToDateTime(dr["ClosedDate"]).ToString("dd-MMM-yyyy");
                    c.closureDesc = Convert.ToString(dr["ClosureDesc"]);
                    c.mCustID = dr["MCustID"].Equals(DBNull.Value) ? null : Convert.ToString(dr["MCustID"]);
                    c.custName = Convert.ToString(dr["CustName"]);
                    c.email = Convert.ToString(dr["Email"]);
                    c.phone = Convert.ToString(dr["Phone"]);
                    c.NRIC = Convert.ToString(dr["NRIC"]);
                    c.caseStatus = Convert.ToString(dr["CaseStatus"]);
                    c.category = Convert.ToString(dr["Category"]);
                    c.subCategory1 = Convert.ToString(dr["SubCategory1"]);
                    c.subCategory2 = Convert.ToString(dr["SubCategory2"]);
                    c.feedbackType = Convert.ToString(dr["FeedbackType"]);
                    c.channel = Convert.ToString(dr["Channel"]);
                    c.caseAddress = Convert.ToString(dr["CaseAddress"]);
                    c.customerAddress = Convert.ToString(dr["CustomerAddress"]);
                    c.postalCode = Convert.ToString(dr["PostalCode"]);
                    //c.customerPostalCode = Convert.ToString(dr["CustomerPostalCode"]);
                    c.longitude = dr["Longitude"].Equals(DBNull.Value) ? null : (Double?)dr["Longitude"];//?
                    c.latitude = dr["Latitude"].Equals(DBNull.Value) ? null : (Double?)dr["Latitude"];//?
                    c.subject = Convert.ToString(dr["Subject"]);
                    c.description = Convert.ToString(dr["Description"]);
                    c.source = Convert.ToString(dr["Source"]);
                    c.timesRouted = Convert.ToInt16(dr["TimesRouted"]);
                    c.isViewed = Convert.ToBoolean(dr["IsViewed"]);
                    c.caseOwner = Convert.ToString(dr["CaseOwner"]);
                    c.jointID = Convert.ToString(dr["JointID"]);
                    //c.jointID = dr["CustomerAddress"].Equals(DBNull.Value) ? "" : (string)dr["JointID"];
                    c.jointDescription = Convert.ToString(dr["JointDescription"]);
                    c.lastUpdateDateTime = Convert.ToDateTime(dr["LastUpdateDateTime"]).ToString("dd-MMM-yyyy HH:MM");
                    c.lastUpdateAgency = Convert.ToString(dr["LastUpdateAgency"]);
                    c.duplicateMasterCaseID = Convert.ToString(dr["DuplicateMasterCaseID"]);
                    c.duplicateID = dr["DuplicateID"].Equals(DBNull.Value) ? null : (int?)dr["DuplicateID"];//?
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return c;
        }

        public static string GetCasesImage(string caseID)
        {
            string path = string.Empty;
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = ConfigurationManager.
                    ConnectionStrings["MunicipalFeedbackConnectionString1"].ConnectionString;
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT [ImageUrl] FROM [MunicipalFeedback].[dbo].[Attachment] WHERE CaseID=@caseID";
                cmd.Parameters.AddWithValue("@caseID", caseID);

                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    path = Convert.ToString(dr["ImageUrl"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return path;
        }

        /// <summary>
        /// Insert Case info into database
        /// </summary>
        /// <param name="c">Case object</param>
        /// <returns>Rows affected</returns>
        public static int CreateCase(Case c)
        {
            //declare variables-->rows inserted, new sql connection
            int rowsInserted = 0;
            SqlConnection conn = new SqlConnection();
            try
            {
                //set up and open connection
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["MunicipalFeedbackConnectionString1"].ConnectionString;
                conn.Open();
                //construct sql command
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "INSERT INTO [MunicipalFeedback].[dbo].[Case] values("
                    + "@CaseID,@AgencyCaseID,@FeedbackDate,@ReceivedDate,@ClosedDate,@ClosureDesc,@MCustID,@CustName,@Email,@Phone,"
                    + "@NRIC,@CaseStatus,@Category,@SubCategory1,@SubCategory2,@FeedbackType,@Channel,@CaseAddress,@CustomerAddress,@PostalCode,"
                    + "@Longitude,@Latitude,@Subject,@Description,@Source,@TimesRouted,@IsViewed,@CaseOwner,@JointID,@JointDescription,"
                    + "@LastUpdateDateTime,@LastUpdateAgency,@DuplicateMasterCaseID,@DuplicateID,@DueDate);";
                //assign value to variables
                cmd.Parameters.AddWithValue("@CaseID", c.caseID);
                if (c.agencyCaseID == null)
                {
                    cmd.Parameters.AddWithValue("@AgencyCaseID", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@AgencyCaseID", c.agencyCaseID);
                }
                cmd.Parameters.AddWithValue("@FeedbackDate", Convert.ToDateTime(c.feedbackDate));
                cmd.Parameters.AddWithValue("@ReceivedDate", Convert.ToDateTime(c.receivedDate));
                if (c.dueDate == null)
                {
                    cmd.Parameters.AddWithValue("@DueDate", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@DueDate", Convert.ToDateTime(c.dueDate));
                }
                if (c.closedDate == "")
                {
                    cmd.Parameters.AddWithValue("@ClosedDate", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@ClosedDate", Convert.ToDateTime(c.closedDate));
                }
                if (c.closureDesc == null)
                {
                    cmd.Parameters.AddWithValue("@ClosureDesc", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@ClosureDesc", c.closureDesc);
                }
                if (c.mCustID == null)
                {
                    cmd.Parameters.AddWithValue("@MCustID", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@MCustID", c.mCustID);
                }
                if (c.custName == null)
                {
                    cmd.Parameters.AddWithValue("@CustName", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@CustName", c.custName);
                }
                if (c.email == null)
                {
                    cmd.Parameters.AddWithValue("@Email", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@Email", c.email);
                }
                if (c.phone == null)
                {
                    cmd.Parameters.AddWithValue("@Phone", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@Phone", c.phone);
                }
                if (c.NRIC == null)
                {
                    cmd.Parameters.AddWithValue("@NRIC", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@NRIC", c.NRIC);
                }
                cmd.Parameters.AddWithValue("@CaseStatus", c.caseStatus);
                cmd.Parameters.AddWithValue("@Category", c.category);
                if (c.subCategory1 == null)
                {
                    cmd.Parameters.AddWithValue("@SubCategory1", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@SubCategory1", c.subCategory1);
                }
                if (c.subCategory2 == null)
                {
                    cmd.Parameters.AddWithValue("@SubCategory2", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@SubCategory2", c.subCategory2);
                }
                cmd.Parameters.AddWithValue("@FeedbackType", c.feedbackType);
                cmd.Parameters.AddWithValue("@Channel", c.channel);
                if (c.caseAddress == null)
                {
                    cmd.Parameters.AddWithValue("@CaseAddress", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@CaseAddress", c.caseAddress);
                }
                if (c.customerAddress == null)
                {
                    cmd.Parameters.AddWithValue("@CustomerAddress", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@CustomerAddress", c.customerAddress);
                }
                if (c.postalCode == null)
                {
                    cmd.Parameters.AddWithValue("@PostalCode", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@PostalCode", c.postalCode);
                }
                //if (c.customerPostalCode == null)
                //{
                //    cmd.Parameters.AddWithValue("@CustomerPostalCode", DBNull.Value);
                //}
                //else
                //{
                //    cmd.Parameters.AddWithValue("@CustomerPostalCode", c.customerPostalCode);
                //}
                //if (c.longitude == null)
                //{
                    cmd.Parameters.AddWithValue("@Longitude", DBNull.Value);
                //}
                //else
                //{
                //    cmd.Parameters.AddWithValue("@Longitude", c.longitude);
                //}
                //if (c.latitude == null)
                //{
                    cmd.Parameters.AddWithValue("@Latitude", DBNull.Value);
                //}
                //else
                //{
                //    cmd.Parameters.AddWithValue("@Latitude", c.latitude);
                //}
                if (c.subject == null)
                {
                    cmd.Parameters.AddWithValue("@Subject", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@Subject", c.subject);
                }
                cmd.Parameters.AddWithValue("@Description", c.description);
                if (c.source == null)
                {
                    cmd.Parameters.AddWithValue("@Source", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@Source", c.source);
                }
                cmd.Parameters.AddWithValue("@TimesRouted", c.timesRouted);
                cmd.Parameters.AddWithValue("@IsViewed", c.isViewed);
                cmd.Parameters.AddWithValue("@CaseOwner", c.caseOwner);
                if (c.jointID == null)
                {
                    cmd.Parameters.AddWithValue("@JointID", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@JointID", c.jointID);
                }
                if (c.jointDescription == null)
                {
                    cmd.Parameters.AddWithValue("@JointDescription", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@JointDescription", c.jointDescription);
                }
                cmd.Parameters.AddWithValue("@LastUpdateDateTime", Convert.ToDateTime(c.lastUpdateDateTime));
                cmd.Parameters.AddWithValue("@LastUpdateAgency", c.lastUpdateAgency);
                if (c.duplicateMasterCaseID == null)
                {
                    cmd.Parameters.AddWithValue("@DuplicateMasterCaseID", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@DuplicateMasterCaseID", c.duplicateMasterCaseID);
                }
                if (c.duplicateID == null)
                {
                    cmd.Parameters.AddWithValue("@DuplicateID", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@DuplicateID", c.duplicateID);
                }

                //execute command
                rowsInserted = cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            //close sql connection
            conn.Close();
            //return rows inserted
            return rowsInserted;
        }

        /// <summary>
        /// Update Case info into database
        /// </summary>
        /// <param name="c">Case object</param>
        /// <returns>Rows updated</returns>
        public static int UpdateCase(Case c)
        {
            //declare variables-->rows inserted, new sql connection
            int rowsUpdated = 0;
            SqlConnection conn = new SqlConnection();
            try
            {
                //set up and open connection
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["MunicipalFeedbackConnectionString1"].ConnectionString;
                conn.Open();
                //construct sql command
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "UPDATE [MunicipalFeedback].[dbo].[Case] SET "
                    + "[AgencyCaseID]=@AgencyCaseID,[FeedbackDate]=@FeedbackDate,[ReceivedDate]=@ReceivedDate,[ClosedDate]=@ClosedDate,[DueDate]=@DueDate,"
                    + "[ClosureDesc]=@ClosureDesc,[MCustID]=@MCustID,[CustName]=@CustName,[Email]=@Email,[Phone]=@Phone,"
                    + "[NRIC]=@NRIC,[CaseStatus]=@CaseStatus,[Category]=@Category,[SubCategory1]=@SubCategory1,[SubCategory2]=@SubCategory2,"
                    + "[FeedbackType]=@FeedbackType,[Channel]=@Channel,[CaseAddress]=@CaseAddress,[CustomerAddress]=@CustomerAddress,[PostalCode]=@PostalCode,"//[CustomerPostalCode]=@CustomerPostalCode,
                    + "[Subject]=@Subject,[Description]=@Description,"//[Longitude]=@Longitude,[Latitude]=@Latitude,
                    + "[Source]=@Source,[TimesRouted]=@TimesRouted,[IsViewed]=@IsViewed,[CaseOwner]=@CaseOwner,[JointID]=@JointID,"
                    + "[JointDescription]=@JointDescription,[LastUpdateDateTime]=@LastUpdateDateTime,[LastUpdateAgency]=@LastUpdateAgency,[DuplicateMasterCaseID]=@DuplicateMasterCaseID,[DuplicateID]=@DuplicateID "
                    + "WHERE [CaseID]=@CaseID;";
                //assign value to variables
                cmd.Parameters.AddWithValue("@CaseID", c.caseID);
                if (c.agencyCaseID == null)
                {
                    cmd.Parameters.AddWithValue("@AgencyCaseID", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@AgencyCaseID", c.agencyCaseID);
                }
                cmd.Parameters.AddWithValue("@FeedbackDate", Convert.ToDateTime(c.feedbackDate));
                cmd.Parameters.AddWithValue("@ReceivedDate", Convert.ToDateTime(c.receivedDate));
                if (c.dueDate == null)
                {
                    cmd.Parameters.AddWithValue("@DueDate", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@DueDate", Convert.ToDateTime(c.dueDate));
                }
                if (c.closedDate == "")
                {
                    cmd.Parameters.AddWithValue("@ClosedDate", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@ClosedDate", Convert.ToDateTime(c.closedDate));
                }
                if (c.closureDesc == null)
                {
                    cmd.Parameters.AddWithValue("@ClosureDesc", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@ClosureDesc", c.closureDesc);
                }
                if (c.mCustID == null)
                {
                    cmd.Parameters.AddWithValue("@MCustID", DBNull.Value);
                }
                else
                {
                    //Guid myGuid = new Guid("71494dd6-90fb-417d-b9e2-28f34103c040");
                    //string myGuidString = c.mCustID;
                    //cmd.Parameters.AddWithValue("@MCustID", TypeDescriptor.GetConverter(myGuid).ConvertFrom(myGuidString));

                    cmd.Parameters.AddWithValue("@MCustID", new Guid(c.mCustID));
                }
                if (c.custName == null)
                {
                    cmd.Parameters.AddWithValue("@CustName", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@CustName", c.custName);
                }
                if (c.email == null)
                {
                    cmd.Parameters.AddWithValue("@Email", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@Email", c.email);
                }
                if (c.phone == null)
                {
                    cmd.Parameters.AddWithValue("@Phone", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@Phone", c.phone);
                }
                if (c.NRIC == null)
                {
                    cmd.Parameters.AddWithValue("@NRIC", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@NRIC", c.NRIC);
                }
                cmd.Parameters.AddWithValue("@CaseStatus", c.caseStatus);
                cmd.Parameters.AddWithValue("@Category", c.category);
                if (c.subCategory1 == null)
                {
                    cmd.Parameters.AddWithValue("@SubCategory1", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@SubCategory1", c.subCategory1);
                }
                if (c.subCategory2 == null)
                {
                    cmd.Parameters.AddWithValue("@SubCategory2", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@SubCategory2", c.subCategory2);
                }
                cmd.Parameters.AddWithValue("@FeedbackType", c.feedbackType);
                cmd.Parameters.AddWithValue("@Channel", c.channel);
                if (c.caseAddress == null)
                {
                    cmd.Parameters.AddWithValue("@CaseAddress", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@CaseAddress", c.caseAddress);
                }
                if (c.customerAddress == null)
                {
                    cmd.Parameters.AddWithValue("@CustomerAddress", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@CustomerAddress", c.customerAddress);
                }
                if (c.postalCode == null)
                {
                    cmd.Parameters.AddWithValue("@PostalCode", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@PostalCode", c.postalCode);
                }
                //if (c.customerPostalCode == null)
                //{
                //    cmd.Parameters.AddWithValue("@CustomerPostalCode", DBNull.Value);
                //}
                //else
                //{
                //    cmd.Parameters.AddWithValue("@CustomerPostalCode", c.customerPostalCode);
                //}
                //if (c.longitude == null)
                //{
                //    cmd.Parameters.AddWithValue("@Longitude", DBNull.Value);
                //}
                //else
                //{
                //    cmd.Parameters.AddWithValue("@Longitude", c.longitude);
                //}
                //if (c.latitude == null)
                //{
                //    cmd.Parameters.AddWithValue("@Latitude", DBNull.Value);
                //}
                //else
                //{
                //    cmd.Parameters.AddWithValue("@Latitude", c.latitude);
                //}
                if (c.subject == null)
                {
                    cmd.Parameters.AddWithValue("@Subject", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@Subject", c.subject);
                }
                cmd.Parameters.AddWithValue("@Description", c.description);
                if (c.source == null)
                {
                    cmd.Parameters.AddWithValue("@Source", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@Source", c.source);
                }
                cmd.Parameters.AddWithValue("@TimesRouted", c.timesRouted);
                cmd.Parameters.AddWithValue("@IsViewed", c.isViewed);
                cmd.Parameters.AddWithValue("@CaseOwner", c.caseOwner);
                if (c.jointID == null)
                {
                    cmd.Parameters.AddWithValue("@JointID", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@JointID", c.jointID);
                }
                if (c.jointDescription == null)
                {
                    cmd.Parameters.AddWithValue("@JointDescription", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@JointDescription", c.jointDescription);
                }
                cmd.Parameters.AddWithValue("@LastUpdateDateTime", c.lastUpdateDateTime);
                cmd.Parameters.AddWithValue("@LastUpdateAgency", c.lastUpdateAgency);
                if (c.duplicateMasterCaseID == null)
                {
                    cmd.Parameters.AddWithValue("@DuplicateMasterCaseID", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@DuplicateMasterCaseID", c.duplicateMasterCaseID);
                }
                if (c.duplicateID == null)
                {
                    cmd.Parameters.AddWithValue("@DuplicateID", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@DuplicateID", c.duplicateID);
                }

                //execute command
                rowsUpdated = cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            //close sql connection
            conn.Close();
            //return rows inserted
            return rowsUpdated;
        }


        public static bool UpdateCaseDesc(Case c)
        {
            //declare variables-->rows inserted, new sql connection
            int rowsUpdated = 0;
            SqlConnection conn = new SqlConnection();
            try
            {
                //set up and open connection
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["MunicipalFeedbackConnectionString1"].ConnectionString;
                conn.Open();
                //construct sql command
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "UPDATE [MunicipalFeedback].[dbo].[Case] SET [ClosureDesc]=@ClosureDesc,[ClosedDate]=@ClosedDate ,[CaseStatus]=@CaseStatus WHERE [CaseID]=@CaseID;";
                //assign value to variables
                cmd.Parameters.AddWithValue("@CaseID", c.caseID);
                cmd.Parameters.AddWithValue("@ClosureDesc", c.closureDesc);
                cmd.Parameters.AddWithValue("@CaseStatus", c.caseStatus);
                cmd.Parameters.AddWithValue("@ClosedDate", Convert.ToDateTime(c.closedDate));
                //execute command
                rowsUpdated = cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            //close sql connection
            conn.Close();
            bool IsUpdated = false;
            if (rowsUpdated > 0)
            {
                IsUpdated = true;
            }
            return IsUpdated;
        }
    }
}